<?php
namespace App\Models;
use App\Models\BaseModel;
use pdo;
class UserModel extends BaseModel{
	public function __construct(){
		parent::__construct();
		$this->_table="user";
	}
	public function getUserInfo($name,$pas){
		$sql = sprintf("select * from `%s` where `Email` = '%s' and Password='%s'", $this -> _table, $name,$pas);
		return $this -> query($sql);
	}
	public function getUserRolePower($user, $urlpath) {
		$sql = "select ControllerAction FROM user INNER JOIN rolepower on `user`.RoleID = rolepower.RoleID INNER JOIN power on rolepower.Power = power.ID WHERE Email='" . $user . "' and ControllerAction='" . $urlpath . "'";
		return $this -> query($sql);
	}
	public function getUserName($loginName){
     $sql = sprintf("select * from `%s` where `Email` = '%s'", $this -> _table, $loginName);
     $sth =$this -> _dbHandle -> prepare($sql);
	 $sth -> execute();
     return $sth -> fetch();
	}
	public function getEmail($email){
	 $sql = sprintf("select * from `%s` where `Email` = '%s'", $this -> _table, $email);
     $sth =$this -> _dbHandle -> prepare($sql);
	 $sth -> execute();
     return $sth -> fetch();
	}
	public function userMenuMsg($id){
	 $sql="SELECT PId,Lv,PowerName,MenuName,ActionId FROM `user` INNER JOIN rolepower on `user`.Roleid=rolepower.RoleId INNER JOIN power ON rolepower.Power=Power.ID INNER JOIN menu ON menu.ID=Power.ActionId WHERE user.ID='" . $id . "' AND ActionType='menu'";
	 $sth=$this->_dbHandle->prepare($sql);
	 $sth->execute();
	 return  $sth -> fetchAll();
	}
	public function selectUser($name){
	 $sql = sprintf("select * from `%s` where `UserName` = '%s'", $this -> _table, $name);
     $sth =$this -> _dbHandle -> prepare($sql);
	 $sth -> execute();
     return $sth -> fetchAll();
	}
	public function roleList(){
	 $sql = sprintf("select * from role");
     $sth =$this -> _dbHandle -> prepare($sql);
	 $sth -> execute();
     return $sth -> fetchAll();
	}
	public function addRole($rolename,$rolelv){
	 $sql = sprintf("insert into role(ID,RoleName) values ($rolelv,'$rolename')");
     $sth =$this -> _dbHandle -> prepare($sql);
	 $sth -> execute();
     return $sth -> fetchAll();
	}
	public function roleDelete($roleid){
	 $sql = sprintf("delete from role where ID=$roleid");
     $sth =$this -> _dbHandle -> prepare($sql);
	 $sth -> execute();
     return $sth -> fetch();
	}
	public function userrole($id){
	 $sql="SELECT `user`.ID,UserName,Sex,Email,Phone FROM role INNER JOIN `user`ON role.ID=`user`.Roleid WHERE role.ID='" . $id . "'";
	 $sth=$this->_dbHandle->prepare($sql);
	 $sth->execute();
	 return  $sth -> fetchAll();
	}
	public function powerlist($id){
	 $sql="SELECT Power.ID, Power.PowerName,power.ActionType FROM role INNER JOIN rolepower on role.ID=rolepower.RoleId  INNER JOIN power on rolepower.Power=Power.ID where role.ID='" . $id . "'";
	 $sth=$this->_dbHandle->prepare($sql);
	 $sth->execute();
	 return  $sth -> fetchAll();
	}
	public function userRolePower($role,$id){
     $sql="SELECT Power.ID, Power.PowerName,power.ActionType FROM role INNER JOIN rolepower on role.ID=rolepower.RoleId INNER JOIN power on rolepower.Power=Power.ID where role.ID='" . $id . "' AND PowerName='" . $role . "'";
	 $sth=$this->_dbHandle->prepare($sql);
	 $sth->execute();
	 return  $sth -> fetchAll();
	}
	public function getRole(){
	 $sql = sprintf("select ID from role");
     $sth =$this -> _dbHandle -> prepare($sql);
	 $sth -> execute();
     return $sth -> fetchAll();
	}
	public function selectRole($roleID){
	 $sql = sprintf("SELECT * from `user` WHERE Roleid='" . $roleID . "'");
     $sth =$this -> _dbHandle -> prepare($sql);
	 $sth -> execute();
     return $sth -> fetchAll();
	}
	public function roleSelect($roleid){
	 $sql = sprintf("SELECT ID from role WHERE ID='" .$roleid. "'");
     $sth =$this -> _dbHandle -> prepare($sql);
	 $sth -> execute();
     return $sth -> fetchAll();
	}
	public function powerSelect(){
	 $sql = sprintf("SELECT * from power");
     $sth =$this -> _dbHandle -> prepare($sql);
	 $sth -> execute();
     return $sth -> fetchAll();
	}
	public function powerMsgForm($power){
	 $sql = sprintf("SELECT * from power where PowerName='" .$power. "'");
     $sth =$this -> _dbHandle -> prepare($sql);
	 $sth -> execute();
     return $sth -> fetch();
	}
	public function rolepower($roleid,$num){
	 $sql = sprintf("SELECT * from rolepower where RoleId='" .$roleid. "' and Power='" .$num. "'");
     $sth =$this -> _dbHandle -> prepare($sql);
	 $sth -> execute();
     return $sth -> fetch();
	}
	public function insertPowerForm($roleid,$num){
	$sql = sprintf("insert into rolepower(RoleId,Power) values($roleid,$num)");
     $sth =$this -> _dbHandle -> prepare($sql);
	 $sth -> execute();
     return $sth -> rowCount();
	}
	public function deletePower($roleid,$PowerID){
	$sql = sprintf("delete from rolepower where RoleId='" .$roleid. "' and Power='" .$PowerID. "'");
     $sth =$this -> _dbHandle -> prepare($sql);
	 $sth -> execute();
     return $sth -> rowCount();
	}
}
